﻿using Common;
using DAL.DBUtility;
using Model.AntiFake;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL.AntiFake
{
     public class DigitcodeDAL
    {

        #region 分页数据
        /// <summary>
        /// 分页数据
        /// </summary>
        /// <param name="model"></param>
        /// <param name="pager"></param>
        /// <returns></returns>
        public DataTable GetPagerList(ref PagerModel pager, string code)
        {
            DataTable result = new DataTable();
            
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append(" ");
            try
            {
                strSql1.Append("a.ID,");
                strSql1.Append("a.Fw_Code,");
                strSql1.Append("a.Wl_Code,");
                strSql1.Append("a.WB_Fw_Code,");
                strSql1.Append("a.WB_Wl_Code,");
                strSql1.Append("a.EnterpriseId,");
                strSql1.Append("a.Flag,");
                strSql1.Append("a.Code1,");
                strSql1.Append("a.Code2,");
                strSql1.Append("a.Stats,");
                strSql1.Append("a.QueryCount,");
                strSql1.Append("a.QueryTime,");
                strSql1.Append("a.CreateDate,");
                strSql1.Append("a.ChangeDate,");
                strSql1.Append("a.ProductId,");
                strSql1.Append("a.CodeType,");
                strSql1.Append("EnterpriseName,");
                var strSql = new StringBuilder(strSql1.ToString().Trim(','));
                pager.FldName = strSql.ToString();
                pager.TblName = " AntiFake_Digitcode" + code + " a,Organization_Enterprise e ";
                if (string.IsNullOrEmpty(pager.StrCondition))
                {
                    pager.StrCondition = " 1=0   ";
                }

                if (string.IsNullOrEmpty(pager.FldSort) ||  pager.FldSort==" ")
                    pager.FldSort = " a.ID asc ";
                SqlParameter[] parameters = {
					new SqlParameter("@fldName", pager.FldName),
                    new SqlParameter("@tblName", pager.TblName),
                    new SqlParameter("@fldSort", pager.FldSort),
                    new SqlParameter("@strCondition"," a.EnterpriseId=e.ID  and  "+ pager.StrCondition),
                    new SqlParameter("@pageSize", pager.PageSize),
                    new SqlParameter("@page", pager.Page),
                    DbHelperSQL.AddOutParameter("@curpage", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@pageCount", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@counts", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@retval", DbType.Int32, 4),
			    };
                result = DbHelperSQL.RunProcedure("SP_Table_GetPageer", parameters, "dbN").Tables[0];
                if (result != null && result.Rows.Count > 0)
                {
                    pager.Curpage = PageValidate.ConvertToTrimInt(parameters[6].Value);
                    pager.PageSize = PageValidate.ConvertToTrimInt(parameters[7].Value);
                    pager.Counts = PageValidate.ConvertToTrimInt(parameters[8].Value);
                    pager.Retval = PageValidate.ConvertToTrimInt(parameters[9].Value);
                }
            }
            catch (Exception ex)
            {

            }
            return result;
        }
        #endregion

        #region 表是否存在
        public bool TabExists(string code)
        {
            return DbHelperSQL.TabExists("AntiFake_Digitcode" + code);
        }
        #endregion

        #region 查询（获取单个MODEL）
         /// <summary>
        /// 根据主键查询表数据
        /// </summary>
        /// <param name=" ID">string 主键--查询条件</param>
        /// <returns>DataTable 查询结果集</returns>
        public AntiFake_DigitcodeModel GetModel(int ID)
        {
            AntiFake_DigitcodeModel model = new AntiFake_DigitcodeModel();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ");
            strSql.Append("  ID,");
            strSql.Append("Fw_Code ,");
            strSql.Append("Wl_Code ,");
            strSql.Append("WB_Fw_Code ,");
            strSql.Append("WB_Wl_Code ,");
            strSql.Append("EnterpriseId ,");
            strSql.Append("Flag ,");
            strSql.Append("Code1 ,");
            strSql.Append("Code2 ,");
            strSql.Append("Stats ,");
            strSql.Append("QueryCount ,");
            strSql.Append("QueryTime ,");
            strSql.Append("CreateDate ,");
            strSql.Append("ChangeDate ,");
            strSql.Append("ProductId ,");
            strSql.Append("CodeType ,");
            strSql = new StringBuilder(strSql.ToString().Trim(','));
            strSql.Append(" from AntiFake_Digitcode000101 ");
            strSql.Append(" where  ID='" + ID + "'");

            SqlParameter[] parameters = {
					new SqlParameter("@ID",ID)
			};

            SqlDataReader sdr = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            if (sdr.Read())
            {
                model = CommonModel(sdr);
            }
            sdr.Close();
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 添加方法（通过传递实体类添加数据，把添加的数据赋值给实体类属性，不添加的值不填写）
        /// </summary>
        /// <param name="AntiFake_DigitcodeModel">AntiFake_DigitcodeModel实体类</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public Common.ResultEntity Insert(AntiFake_DigitcodeModel model)
        {

            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            int pcount = 0;
            int i = 0;
            if (model.ID != null)
            {
                strSql1.Append("ID,");
                strSql2.Append("@ID ,");
                pcount += 1;
            }
            if (model.Fw_Code != null)
            {
                strSql1.Append("Fw_Code,");
                strSql2.Append("@Fw_Code ,");
                pcount += 1;
            }
            if (model.Wl_Code != null)
            {
                strSql1.Append("Wl_Code,");
                strSql2.Append("@Wl_Code ,");
                pcount += 1;
            }
            if (model.WB_Fw_Code != null)
            {
                strSql1.Append("WB_Fw_Code,");
                strSql2.Append("@WB_Fw_Code ,");
                pcount += 1;
            }
            if (model.WB_Wl_Code != null)
            {
                strSql1.Append("WB_Wl_Code,");
                strSql2.Append("@WB_Wl_Code ,");
                pcount += 1;
            }
            if (model.EnterpriseId != null)
            {
                strSql1.Append("EnterpriseId,");
                strSql2.Append("@EnterpriseId ,");
                pcount += 1;
            }
            if (model.Flag != null)
            {
                strSql1.Append("Flag,");
                strSql2.Append("@Flag ,");
                pcount += 1;
            }
            if (model.Code1 != null)
            {
                strSql1.Append("Code1,");
                strSql2.Append("@Code1 ,");
                pcount += 1;
            }
            if (model.Code2 != null)
            {
                strSql1.Append("Code2,");
                strSql2.Append("@Code2 ,");
                pcount += 1;
            }
            if (model.Stats != null)
            {
                strSql1.Append("Stats,");
                strSql2.Append("@Stats ,");
                pcount += 1;
            }
            if (model.QueryCount != null)
            {
                strSql1.Append("QueryCount,");
                strSql2.Append("@QueryCount ,");
                pcount += 1;
            }
            if (model.QueryTime != null)
            {
                strSql1.Append("QueryTime,");
                strSql2.Append("@QueryTime ,");
                pcount += 1;
            }
            if (model.CreateDate != null)
            {
                strSql1.Append("CreateDate,");
                strSql2.Append("@CreateDate ,");
                pcount += 1;
            }
            if (model.ChangeDate != null)
            {
                strSql1.Append("ChangeDate,");
                strSql2.Append("@ChangeDate ,");
                pcount += 1;
            }
            if (model.ProductId != null)
            {
                strSql1.Append("ProductId,");
                strSql2.Append("@ProductId ,");
                pcount += 1;
            }
            if (model.CodeType != null)
            {
                strSql1.Append("CodeType,");
                strSql2.Append("@CodeType ,");
                pcount += 1;
            }
            SqlParameter[] parameters = 
                {
					new SqlParameter("@Fw_Code", model.Fw_Code),
					new SqlParameter("@Wl_Code", model.Wl_Code),
					new SqlParameter("@WB_Fw_Code", model.WB_Fw_Code),
					new SqlParameter("@WB_Wl_Code", model.WB_Wl_Code),
					new SqlParameter("@EnterpriseId", model.EnterpriseId),
					new SqlParameter("@Flag", model.Flag),
					new SqlParameter("@Code1", model.Code1),
					new SqlParameter("@Code2", model.Code2),
					new SqlParameter("@Stats", model.Stats),
					new SqlParameter("@QueryCount", model.QueryCount),
					new SqlParameter("@QueryTime", model.QueryTime),
					new SqlParameter("@CreateDate", model.CreateDate),
					new SqlParameter("@ChangeDate", model.ChangeDate),
					new SqlParameter("@ProductId", model.ProductId),
					new SqlParameter("@CodeType", model.CodeType),
                 };



            strSql.Append("insert into AntiFake_Digitcode000101(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            return DbHelperSQL.CommonResult(DbHelperSQL.GetSingle(strSql.ToString().Trim(), parameters));
        }
        #endregion

        #region 修改
        /// <summary>
        /// 根据主键修改数据修改
        /// </summary>
        /// <param name=" ID"> AntiFake_DigitcodeModel实体类 需要修改的列赋值给实体类的属性,不修改的不填写</param>
        /// <returns>int 0为失败 ;1为成功</returns>
        public Common.ResultEntity Update(AntiFake_DigitcodeModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update AntiFake_Digitcode000101 set ");
            if (model.Fw_Code != null)
            {
                strSql.Append("Fw_Code='" + model.Fw_Code + "',");
            }
            if (model.Wl_Code != null)
            {
                strSql.Append("Wl_Code='" + model.Wl_Code + "',");
            }
            if (model.WB_Fw_Code != null)
            {
                strSql.Append("WB_Fw_Code='" + model.WB_Fw_Code + "',");
            }
            if (model.WB_Wl_Code != null)
            {
                strSql.Append("WB_Wl_Code='" + model.WB_Wl_Code + "',");
            }
            if (model.EnterpriseId != null)
            {
                strSql.Append("EnterpriseId='" + model.EnterpriseId + "',");
            }
            if (model.Flag != null)
            {
                strSql.Append("Flag='" + model.Flag + "',");
            }
            if (model.Code1 != null)
            {
                strSql.Append("Code1='" + model.Code1 + "',");
            }
            if (model.Code2 != null)
            {
                strSql.Append("Code2='" + model.Code2 + "',");
            }
            if (model.Stats != null)
            {
                strSql.Append("Stats='" + model.Stats + "',");
            }
            if (model.QueryCount != null)
            {
                strSql.Append("QueryCount='" + model.QueryCount + "',");
            }
            if (model.QueryTime != null)
            {
                strSql.Append("QueryTime=CONVERT(datetime,'" + model.QueryTime + "'),");
            }
            if (model.CreateDate != null)
            {
                strSql.Append("CreateDate=CONVERT(datetime,'" + model.CreateDate + "'),");
            }
            if (model.ChangeDate != null)
            {
                strSql.Append("ChangeDate=CONVERT(datetime,'" + model.ChangeDate + "'),");
            }
            if (model.ProductId != null)
            {
                strSql.Append("ProductId='" + model.ProductId + "',");
            }
            if (model.CodeType != null)
            {
                strSql.Append("CodeType='" + model.CodeType + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where ID='" + model.ID + "' ");
            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(strSql.ToString()));
        }
        #endregion
        
        #region 删除
        /// <summary>
        /// 通过主键删除方法
        /// </summary>
        /// <param name="  ID">string 主键--删除条件</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public Common.ResultEntity Delete(int? ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from AntiFake_Digitcode000101 ");
            strSql.Append(" where  ID= '" + ID + "'");
            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(strSql.ToString()));
        }
        #endregion

        #region 公共Model
        /// <summary>
        /// 公共Model
        /// </summary>
        /// <param name="result"></param>
        /// <returns></returns>
        private AntiFake_DigitcodeModel CommonModel(SqlDataReader sdr)
        {
            AntiFake_DigitcodeModel model = new AntiFake_DigitcodeModel();
            model.ID = PageValidate.ConvertToTrimInt(sdr["ID"]);
            model.Fw_Code = PageValidate.ConvertToTrimString(sdr["Fw_Code"]);
            model.Wl_Code = PageValidate.ConvertToTrimString(sdr["Wl_Code"]);
            model.WB_Fw_Code = PageValidate.ConvertToTrimString(sdr["WB_Fw_Code"]);
            model.WB_Wl_Code = PageValidate.ConvertToTrimString(sdr["WB_Wl_Code"]);
            model.EnterpriseId = PageValidate.ConvertToTrimInt(sdr["EnterpriseId"]);
            model.Flag = PageValidate.ConvertToTrimInt(sdr["Flag"]);
            model.Code1 = PageValidate.ConvertToTrimString(sdr["Code1"]);
            model.Code2 = PageValidate.ConvertToTrimString(sdr["Code2"]);
            model.Stats = PageValidate.ConvertToTrimInt(sdr["Stats"]);
            model.QueryCount = PageValidate.ConvertToTrimInt(sdr["QueryCount"]);
            model.QueryTime = PageValidate.ConvertToDateTime(sdr["QueryTime"]);
            model.CreateDate = PageValidate.ConvertToDateTime(sdr["CreateDate"]);
            model.ChangeDate = PageValidate.ConvertToDateTime(sdr["ChangeDate"]);
            model.ProductId = PageValidate.ConvertToTrimInt(sdr["ProductId"]);
            model.CodeType = PageValidate.ConvertToTrimString(sdr["CodeType"]);
            model.EnterpriseName = PageValidate.ConvertToTrimString(sdr["EnterpriseName"]);
            return model;
        }
        #endregion

        #region 根据物流码  查询防伪码
        /// <summary>
        /// 根据物流码  查询防伪码
        /// </summary>
        /// <returns></returns>
        public string GetDigitFw_CodeData(string tbl, string Wl_Code)
        {
            string sql = string.Format("select Fw_Code from AntiFake_Digitcode{0} where Wl_Code='{1}'", tbl, Wl_Code);
            return DbHelperSQL.GetSingle(sql).ToString();
        }
        #endregion

        #region 根据防伪码  查询物流码
        /// <summary>
        ///根据防伪码  查询物流码
        /// </summary>
        /// <returns></returns>
        public string GetDigitWl_Code(string tbl, string Fw_Code)
        {
            string sql = string.Format("select Wl_Code from AntiFake_Digitcode{0} where Fw_Code='{1}'", tbl, Fw_Code);
            return DbHelperSQL.GetSingle(sql).ToString();
        }
        #endregion

        #region 查询是否有对应防伪码
        public DataTable GetModel(string tablePart, string Fw_Code, string Wl_Code, string ipaddress, string privince, string city)
        {

            string sql = string.Format(@"select ad.*,pp.ProductName,oe.EnterpriseName from AntiFake_Digitcode{0} ad
               left join Product_Product pp on ad.ProductId=pp.ID  left join Organization_Enterprise oe on oe.ID=ad.EnterpriseId 
               where ad.Fw_CodeSecret=substring(ad.Fw_CodeSecret,1,len(ad.Fw_CodeSecret)-4) + '{1}' 
               and ad.Wl_Code='{2}'", tablePart, Fw_Code, Wl_Code);
            DataTable tbl = DbHelperSQL.GetTable(sql);
            string insertSql=string.Empty;
            if (tbl != null && tbl.Rows.Count > 0)
            {
                //向查询记录表中插入查询记录
                insertSql = @"insert into AntiFake_QueryCodeLog(Fw_Code,QueryType,QueryValue,QueryTime,QueryMessage,ProductId,EnterpriseId,QueryProvince,QueryCity) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')";
                insertSql = string.Format(insertSql, tbl.Rows[0]["Fw_CodeSecret"], 1, ipaddress, DateTime.Now, "1", tbl.Rows[0]["ProductId"], tbl.Rows[0]["EnterpriseId"], privince, city);
                DbHelperSQL.ExecuteSql(insertSql);
            }
           
            return DbHelperSQL.GetTable(sql);
        }
        #endregion
         /// <summary>
         /// 根据溯源码查询实体类
         /// </summary>
         /// <returns></returns>
        public DataTable GetModelByWlCode(string tabName, string wl_code)
        {
            string sql = @"select * from {0} where Wl_Code='{1}' ";
            sql = string.Format(sql,AppConst.TablePrefix+tabName,wl_code);
            DataTable result = DbHelperSQL.GetTable(sql);
            return result;
        }

        public bool UpdateCount(string tablePart, string count, string id, string flag)
        {
            string updateSql=string.Empty;
            int countInt =1 ;
            if (!string.IsNullOrEmpty(count))
            {
                countInt = Convert.ToInt32(count)+1;
            }
            if (flag == "both")
            {
                updateSql = @"update AntiFake_Digitcode{0} set QueryCount='{1}',QueryTime='{2}' where ID='{3}'";
                updateSql = string.Format(updateSql, tablePart, countInt, DateTime.Now, id);
            }
            else
            {
                updateSql = @"update AntiFake_Digitcode{0} set QueryCount='{1}'  where ID='{2}'";
                updateSql = string.Format(updateSql, tablePart, countInt, id);
            }
            if(DbHelperSQL.ExecuteSql(updateSql)>=1){
                return true;
            }
            else
                return false;
        }
         /// <summary>
         /// 获取查询记录
         /// </summary>
         /// <param name="code"></param>
         /// <returns></returns>
        public DataTable getQueryLog(string code) {
            string querySql = @"select * from AntiFake_QueryCodeLog where Fw_Code='"+code+"'";
            DataTable tbl = DbHelperSQL.GetTable(querySql);
            return tbl;
        }
    }
}
